'-------------------------------------------------------------------------
' Archive v1.0 --- written Feb. 2007 by CHR.BEST
'
' A demo program how to handle BLOB columns with Rapid-Q/SQLite3.
' Additional, the BLOB data will compressed before stored into the
' database. In this sample program, files are used as BLOB's
' (Binary Large OBjects). Of course, BLOB's can be every kind of binary
' data such as pictures or videos and so on.
' I also used a small DLL (i've made with FreeBasic) to convert fast
' from pure binary strings to plain hex strings (and back) to put the data
' into the database. This is neccessary because a Rapid-Q-Loop would be
' too slow.
'
'-------------------------------------------------------------------------

$APPTYPE console
$include "rapidq2.inc"
$include "sqlite3.inc"

' Pack functions from the free zlib-dll
declare function compress Lib "zlibwapi.dll" alias "compress" (lpDestBuff as Long, byref destLen as long, lpSourceBuff as Long, byval sourceLen as long) as integer
declare function uncompress Lib "zlibwapi.dll" alias "uncompress" (lpDestBuff as Long, byref destLen as long, lpSourceBuff as Long, byval sourceLen as long) as integer

' Hex-Binary-Convert-DLL (made with FreeBasic !)
DECLARE SUB Hex2Bin lib "hexconvert" ALIAS "Hex2Bin@12" (byref hexstr as String, byval myData AS Integer, byref binstr as String)
DECLARE SUB Bin2Hex lib "hexconvert" ALIAS "Bin2Hex@12" (byref binstr as String, byval myData AS Integer, byref hexstr as String)

' EXEC-function from the free sqlite3 DLL (needed for workaround - see below)
declare function sqlite3_exec LIB "sqlite3.dll" alias "sqlite3_exec" _
	(db as long, byref sql as string, pCallback as long, pUserData as long, ppErrMsg as long) as long

' Variable declaration
dim Table as QStringGrid, File as QFileStream
defstr Error, Row, Filedata, sql, NewData
deflng r,z,db

' Main program
if CommandCount = 2 then

' Create database if not exists
 if left$(Command$(1),1) = "/" then swap Command$(1),Command$(2)
 if fileexists("demo.db3") = 0 then r = sql3comm("create table Filearchive(Name VARCHAR(255) PRIMARY KEY, Size BIGINT, Time CHAR(19) DEFAULT CURRENT_TIMESTAMP, Document BLOB)","demo.db3",Error,Table)
 
' Put file into the database
 if ucase$(Command$(2)) = "/P" and fileexists(Command$(1)) <> 0 then
  File.open(Command$(1),fmOpenRead)
  Filedata = File.ReadBinStr(File.Size)
  File.close

  ' Pack the data
  r = len(Filedata)
  z = r*1.002 + 12 + 1
  NewData = space$(z)
  db = compress(varptr(NewData),z,varptr(Filedata),r)
  Filedata = left$(NewData,z)
  Row = Space$(z*2)
  Bin2Hex(Filedata,z,Row)
  
  ' Workaround, because SQL3comm can not handle query strings longer than 255 chars
  sql = "replace into Filearchive(Name, Size, Document) values ('"+Command$(1)+"', "+str$(r)+", X'"+Row+"')"
  sqlite3_open("demo.db3",varptr(db))
  r = sqlite3_exec(db,sql,0,0,0)
  sqlite3_close(db)
  ' End of Workaround

  if r = SQLITE3_OK then
   print Command$(1)+" was written into the database"
  else
   print sql3ErrMSG(r)
  end if
 end if


' Get file from the database
 if ucase$(Command$(2)) = "/G" then
  r = sql3comm("select quote(Document), Size from Filearchive where Name = '"+Command$(1)+"'","demo.db3",Error,Table)
  if r = 0 then:print "File not found":Application.terminate:end if
  r = val(Table.Cell(1,1))
  Row = mid$(Table.Cell(0,1),3,len(Table.Cell(0,1))-3)
  z = len(Row)
  Filedata = space$(z/2)
  Hex2Bin(Row,z,Filedata)

  ' Unpack the data
  NewData = space$(r)
  db = uncompress(varptr(NewData),r,varptr(Filedata),len(Filedata))
  
  File.open(Command$(1),fmCreate)
  File.write(NewData)
  File.close
  print Command$(1)+" was read from the database"
 end if
 
 
' List files in the database
 elseif ucase$(Command$(1)) = "/L" then
  r = sql3comm("select Name, Size, Time from Filearchive","demo.db3",Error,Table)
  for z = 1 to r
   print Table.Cell(0,z)+chr$(9)+Table.Cell(1,z)+chr$(9)+Table.Cell(2,z)
  next

 else
  print
  print "SQLite3/Blob/Zlib/Freebasic-DLL-Demo v1.0  (now with Vitamin C! ;-)"
  print chr$(9)+"--- written by CHR.BEST ---"
  print
  print "Usage: archive [filename] [/P|/G|/L]"
  print " /P - Put file into the Database demo.db3"
  print " /G - Get file from the Database demo.db3"
  print " /L - List files from the Database demo.db3"
 
end if
